﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DapperEx;
using System.Text.RegularExpressions;
using System.Data.SqlClient;

namespace Dapper
{
    public static class DapperEx
    {
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="t"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static bool Insert<T>(this DbBase dbs, T t, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                var sql = SqlQuery<T>.Builder(dbs);
                var flag = db.Execute(sql.InsertSql, t, transaction, commandTimeout);
                return flag == 1;
            }
        }
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="t"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static int InsertAndReturnID<T>(this DbBase dbs, T t, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                var sql = SqlQuery<T>.Builder(dbs);
                object flag = db.ExecuteScalar(string.Format("{0} ; {1}", sql.InsertSql, sql.IdentitySql), t, transaction, commandTimeout);
                if (flag == null)
                {
                    return 0;
                }
                return Convert.ToInt32(flag);
            }
        }
        /// <summary>
        ///  批量插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="lt"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static bool InsertBatch<T>(this DbBase dbs, IEnumerable<T> lt, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                if (transaction == null)
                {
                    transaction = db.BeginTransaction();
                }
                bool ret = false;
                try
                {
                    var sql = SqlQuery<T>.Builder(dbs);
                    var flag = db.Execute(sql.InsertSql, lt, transaction, commandTimeout);
                    ret = (flag == lt.Count());

                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                }
                return ret;
            }
        }

        /// <summary>
        /// 批量插入数据(通过SqlBulkCopy)(只支持sql)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="dt"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout">默认60秒</param>
        /// <returns></returns>
        public static bool InsertBatchDt<T>(this DbBase dbs, DataTable dt, IDbTransaction transaction = null, int commandTimeout = 60) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                if (transaction == null)
                {
                    transaction = db.BeginTransaction();
                }
                bool ret = false;
                try
                {
                    using (System.Data.SqlClient.SqlBulkCopy sqlBC = new System.Data.SqlClient.SqlBulkCopy((SqlConnection)dbs.DbConnecttion, System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity, (SqlTransaction)transaction))
                    {
                        var sql = SqlQuery<T>.Builder(dbs);
                        var columns = Common.GetExecColumns(sql._ModelDes);
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            if (columns.Count(info => info.ColumnName == dt.Columns[i].ColumnName) == 0)
                            {
                                dt.Columns.Remove(dt.Columns[i].ColumnName);
                            }
                        }

                        sqlBC.BatchSize = 50000;
                        sqlBC.BulkCopyTimeout = commandTimeout;
                        sqlBC.DestinationTableName = sql._ModelDes.TableName;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            sqlBC.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                        }

                        sqlBC.WriteToServer(dt);
                        ret = true;
                    }
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                }
                return ret;
            }
        }

        /// <summary>
        /// 按条件删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool Delete<T>(this DbBase dbs, SqlQuery sql) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                if (sql == null)
                {
                    sql = SqlQuery<T>.Builder(dbs);
                }
                var f = db.Execute(sql.DeleteSql, sql.Param);
                return f > 0;
            }
        }
        /// <summary>
        /// 按条件删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool Delete<T>(this DbBase dbs, string whereCase = null, dynamic param = null) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                SqlQuery sql = SqlQuery<T>.Builder(dbs);
                sql.WhereSql = whereCase;
                var f = db.Execute(sql.DeleteSql, (object)param);
                return f > 0;
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="t">如果sql为null，则根据t的主键进行修改</param>
        /// <param name="sql">按条件修改</param>
        /// <returns></returns>
        public static bool Update<T>(this DbBase dbs, T t, SqlQuery sql) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                if (sql == null)
                {
                    sql = SqlQuery<T>.Builder(dbs);
                }
                sql = sql.AppendParam<T>(t);
                var f = db.Execute(sql.UpdateSql, sql.Param);
                return f > 0;
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="t">如果sql为null，则根据t的主键进行修改</param>
        /// <param name="sql">按条件修改</param>
        /// <returns></returns>
        public static bool Update<T>(this DbBase dbs, T t, string whereCase = null, dynamic param = null) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                SqlQuery sql = SqlQuery<T>.Builder(dbs);
                sql = sql.AppendParam<T>(t, param);
                sql.WhereSql = whereCase;
                var f = db.Execute(sql.UpdateSql, sql.Param);
                return f > 0;
            }
        }
        /// <summary>
        /// 获取默认一条数据，没有则为NULL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static T SingleOrDefault<T>(this DbBase dbs, SqlQuery sql) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                if (sql == null)
                {
                    sql = SqlQuery<T>.Builder(dbs);
                }
                sql = sql.Top(1);
                var result = db.Query<T>(sql.QuerySql, sql.Param);
                return result.FirstOrDefault();
            }
        }
        /// <summary>
        /// 获取默认一条数据，没有则为NULL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static T SingleOrDefault<T>(this DbBase dbs, string whereCase = null, dynamic param = null) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                SqlQuery sql = SqlQuery<T>.Builder(dbs);
                sql.WhereSql = whereCase;
                sql.TopNum = 1;
                var result = db.Query<T>(sql.QuerySql, (object)param);
                return result.FirstOrDefault();
            }
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="dataCount"></param>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public static IEnumerable<T> Page<T>(this DbBase dbs, int pageIndex, int pageSize, out long dataCount, SqlQuery sqlQuery = null) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                dataCount = 0;
                if (sqlQuery == null)
                {
                    sqlQuery = SqlQuery<T>.Builder(dbs);
                }
                sqlQuery = sqlQuery.Page(pageIndex, pageSize);
                var para = sqlQuery.Param;
                var cr = db.Query(sqlQuery.CountSql, para).SingleOrDefault();
                dataCount = (long)cr.DataCount;
                IEnumerable<T> result = db.Query<T>(sqlQuery.PageSql, para);
                return result;
            }
        }
        /// <summary>
        ///  分页查询(自定义SQL)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="tableName">表名</param>
        /// <param name="currentPageIndex">当前页索引</param>
        /// <param name="pageSize">每页记录</param>
        /// <param name="dataCount">查询的记录总数</param>
        /// <param name="idFieldName">分页字段（默认主键）</param>
        /// <param name="whereCase">where语句</param>
        /// <param name="sortOrder">排序字段</param>
        /// <returns></returns>
        public static IEnumerable<T> PageBySql<T>(this DbBase dbs, string tableName, int currentPageIndex, int pageSize, out long dataCount, string idFieldName = "", string whereCase = null, string whereParam = null, SortOrder sortOrder = SortOrder.Ascending) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                SqlQuery sql = SqlQuery<T>.Builder(dbs);
                sql.WhereSql = whereCase;
                string pageSql = sql.BuildPageSql(tableName, currentPageIndex, pageSize, idFieldName, sortOrder);
                IEnumerable<T> result = db.Query<T>(pageSql, whereParam).ToList();

                if (!string.IsNullOrEmpty(tableName))
                {
                    sql._ModelDes.TableName = tableName;
                }
                var cr = db.Query(sql.CountSql, sql.Param).SingleOrDefault();
                dataCount = (long)cr.DataCount;
                return result;
            }
        }
        /// <summary>
        ///  分页查询(存储过程)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="tableName">表名</param>
        /// <param name="currentPageIndex">当前页索引</param>
        /// <param name="pageSize">每页记录</param>
        /// <param name="dataCount">查询的记录总数</param>
        /// <param name="idFieldName">分页字段（默认主键）</param>
        /// <param name="whereCase">where语句</param>
        /// <param name="sortOrder">排序字段</param>
        /// <returns></returns>
        public static IEnumerable<T> PageByProc<T>(this DbBase dbs, string tableName, int currentPageIndex, int pageSize, out long dataCount, string idFieldName = "", string whereCase = null, SortOrder sortOrder = SortOrder.Ascending) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                DynamicParameters dp = new DynamicParameters();
                dp.Add("@tblName", tableName);
                dp.Add("@fldOrderName", idFieldName);
                dp.Add("@pageIndex", currentPageIndex);
                dp.Add("@pageSize", pageSize);
                dp.Add("@orderType", (int)sortOrder);
                dp.Add("@strWhere", whereCase);
                dp.Add("@totalCount", dbType: DbType.Int32, direction: ParameterDirection.InputOutput);

                IEnumerable<T> result = db.Query<T>("sp_All_Pager", dp, commandType: CommandType.StoredProcedure).ToList();
                dataCount = dp.Get<int>("@totalCount");
                return result;
            }
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static IEnumerable<T> Query<T>(this DbBase dbs, SqlQuery sql) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                if (sql == null)
                {
                    sql = SqlQuery<T>.Builder(dbs);
                }
                var result = db.Query<T>(sql.QuerySql, sql.Param);
                return result;
            }
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static IEnumerable<T> Query<T>(this DbBase dbs, string whereCase = null, dynamic param = null) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                SqlQuery sql = SqlQuery<T>.Builder(dbs);
                sql.WhereSql = whereCase;
                var result = db.Query<T>(sql.QuerySql, (object)param);
                return result;
            }
        }

        /// <summary>
        /// 数据数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static long Count<T>(this DbBase dbs, SqlQuery sql) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                if (sql == null)
                {
                    sql = SqlQuery<T>.Builder(dbs);
                }
                var cr = db.Query(sql.CountSql, sql.Param).SingleOrDefault();
                return (long)cr.DataCount;
            }
        }
        /// <summary>
        /// 数据数量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbs"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static long Count<T>(this DbBase dbs, string whereCase = null, dynamic param = null) where T : class
        {
            using (var db = dbs.DbConnecttion)
            {
                SqlQuery sql = SqlQuery<T>.Builder(dbs);
                sql.WhereSql = whereCase;
                var cr = db.Query(sql.CountSql, (object)param).SingleOrDefault();
                return (long)cr.DataCount;
            }
        }
    }
}
